package com.sky.service.impl;

import com.github.xiaoymin.knife4j.core.util.StrUtil;
import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrderDetailMapper;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.OrderService;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.Servlet;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ReportServiceImpl implements ReportService {
    private static final Logger log = LoggerFactory.getLogger(ReportServiceImpl.class);
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private OrderDetailMapper orderDetailMapper;
    @Autowired
    private WorkspaceService workspaceService;
    /**
     * 营业额统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end) {
        TurnoverReportVO turnoverReportVO = new TurnoverReportVO();
        //1.存放日期的集合
        // 创建一个List集合，用于存放日期
        List<LocalDate> dateList = new ArrayList<>();
        for (LocalDate date = begin; !date.isAfter(end); date = date.plusDays(1)) {
            dateList.add(date);
        }
        //将datelist集合转化为 中间由 , 隔开的 字符串
        turnoverReportVO.setDateList(StringUtils.join( dateList,","));

        //2.存放营业额集合
        // 创建一个List集合，用于存放营业额
        List<Double> turnoverList = new ArrayList<>();
        for (LocalDate date : dateList) {
            //查询date日期对应的营业额 状态为 5 求的是 已完成的订单
            //select sum(amount) from orders where order_time >= begin and order_time < end and status = 5
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            //创建一个map集合 封装查询条件
            Map map = new HashMap();
            map.put("begin", beginTime);
            map.put("end", endTime);
            map.put("status", Orders.COMPLETED);
            //获取营业额
            Double turnover = orderMapper.getSumByMap(map);
            turnover = turnover == null ? 0.0 : turnover;
            turnoverList.add(turnover);
        }
        turnoverReportVO.setTurnoverList(StringUtils.join( turnoverList,","));
        return turnoverReportVO;
    }

    /**
     * 用户统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public UserReportVO getUserStatistics(LocalDate begin, LocalDate end) {
        UserReportVO userReportVO = new UserReportVO();
        //1.存放日期的集合
        List<LocalDate> dateList = new ArrayList<>();
        for (LocalDate date = begin; !date.isAfter(end); date = date.plusDays(1)) {
            dateList.add(date);
        }
        userReportVO.setDateList(StringUtils.join( dateList,","));
        //2.存放每天 用户总量集合
        //select count(id) from user where create_time < end
        //需观察 创建时间字段 在 end之前
        List<Integer> totalUserList = new ArrayList<>();
        for (LocalDate date :dateList) {
            //将 时间 细节到 分钟秒钟
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            Map map = new HashMap();
            //不用 写 beginTime
            map.put("end", endTime);
            Integer total = userMapper.countByMap(map);
            total = total == null ? 0 : total;
            totalUserList.add(total);
        }
        userReportVO.setTotalUserList(StringUtils.join( totalUserList,","));

        //3.存放每天 新增用户集合
        // select count(id) from user where create_time > begin and create_time < end
        // 只需观察 注册 时间字段 在 begin 和 end之间
        List<Integer> newUserList = new ArrayList<>();
        for (LocalDate date :dateList) {
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            Map map = new HashMap();
            map.put("begin", beginTime);
            map.put("end", endTime);
            Integer NewUser = userMapper.countByMap(map);
            NewUser = NewUser == null ? 0 : NewUser;
            newUserList.add(NewUser);
        }
        userReportVO.setNewUserList(StringUtils.join( newUserList,","));
        return userReportVO;
    }

    /**
     * 订单统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public OrderReportVO getOrdersStatistics(LocalDate begin, LocalDate end) {
        OrderReportVO orderReportVO = new OrderReportVO();
        //1.存放日期的集合
        List<LocalDate> dateList = new ArrayList<>();
        for (LocalDate date = begin; !date.isAfter(end); date = date.plusDays(1)) {
            dateList.add(date);
        }
        orderReportVO.setDateList(StringUtils.join( dateList,","));

        //2.存放 每日订单总数集合 总订单数
        List<Integer> orderCountList = new ArrayList<>();
        Integer totalOrderCount=0;
        for (LocalDate date :dateList) {
            //select count(id) from orders where order_time > begin and order_time < end
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            Map map = new HashMap();
            map.put("begin", beginTime);
            map.put("end", endTime);
            Integer total = orderMapper.countByMap(map);
            total = total == null ? 0 : total;
            orderCountList.add(total);
            totalOrderCount += total;
        }
        orderReportVO.setOrderCountList(StringUtils.join( orderCountList,","));
        orderReportVO.setTotalOrderCount(totalOrderCount);

        //3.存放 每日有效订单数集合
        List<Integer> validOrderCountList = new ArrayList<>();
        Integer validOrderCount=0;
        for (LocalDate date :dateList) {
            //select count(id) from orders where order_time > begin and order_time < end and status = 5
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            Map map = new HashMap();
            map.put("begin", beginTime);
            map.put("end", endTime);
            map.put("status", Orders.COMPLETED);
            Integer total = orderMapper.countByMap(map);
            total = total == null ? 0 : total;
            validOrderCountList.add(total);
            validOrderCount += total;
        }
        orderReportVO.setValidOrderCountList(StringUtils.join( validOrderCountList,","));
        orderReportVO.setValidOrderCount(validOrderCount);
        //4.存放 订单完成率
        orderReportVO.setOrderCompletionRate(0.0);
        if (totalOrderCount != 0)
            orderReportVO.setOrderCompletionRate(validOrderCount.doubleValue()/totalOrderCount);
        return orderReportVO;
    }

    /**
     * 销量排名 top10
     * @param begin
     * @param end
     * @return
     */
    @Override
    public SalesTop10ReportVO getTop10(LocalDate begin, LocalDate end) {
        SalesTop10ReportVO salesTop10ReportVO = new SalesTop10ReportVO();
        List nameList = new ArrayList<>();
        List numberList = new ArrayList<>();
        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);
        Map map = new HashMap();
        map.put("begin", beginTime);
        map.put("end", endTime);

        List<GoodsSalesDTO> top10 = orderDetailMapper.getTop10ByMap(map);
        for (GoodsSalesDTO goodsSalesDTO : top10) {
            nameList.add(goodsSalesDTO.getName());
            numberList.add(goodsSalesDTO.getNumber());
        }
        salesTop10ReportVO.setNameList(StringUtils.join( nameList,","));
        salesTop10ReportVO.setNumberList(StringUtils.join( numberList,","));
        return salesTop10ReportVO;
    }

    /**
     * 导出运营数据报表Excel
     */
    @Override
    public void exportExcel(HttpServletResponse httpServletResponse) {

        //1.获取到数据库关于 所需要的 数据
        //先获取到开始时间和结束时间
        LocalDate begin = LocalDate.now().minusDays(30);
        LocalDate end = LocalDate.now().minusDays(1);
        //查询概览数据
        BusinessDataVO businessDataVO = workspaceService.getBusinessData(LocalDateTime.of(begin, LocalTime.MIN), LocalDateTime.of(end, LocalTime.MAX));

        //2.获取到 excel 运营数据报表模板
        //在项目中resources通过反射来获得excel模板资源  通过poi流 inputStream
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");

        //3.基于 模板 创建excel对象 XSSFWorkbook 获取sheet 再创建行 再创建单元格
        try {
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = excel.getSheetAt(0);

       //4.向excel 模板 中写入数据
            //填充数据--时间
            sheet.getRow(1).getCell(1).setCellValue("时间：" + begin + "至" + end);

            //获得第4行
            XSSFRow row = sheet.getRow(3);
            row.getCell(2).setCellValue(businessDataVO.getTurnover());
            row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());
            row.getCell(6).setCellValue(businessDataVO.getNewUsers());

            //获得第5行
            row = sheet.getRow(4);
            row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());
            row.getCell(4).setCellValue(businessDataVO.getUnitPrice());

            //填充明细数据
            for (int i = 0; i < 30; i++) {
                LocalDate date = begin.plusDays(i);
                //查询某一天的营业数据
                BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));

                //获得某一行
                row = sheet.getRow(7 + i);
                row.getCell(1).setCellValue(date.toString());
                row.getCell(2).setCellValue(businessData.getTurnover());
                row.getCell(3).setCellValue(businessData.getValidOrderCount());
                row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
                row.getCell(5).setCellValue(businessData.getUnitPrice());
                row.getCell(6).setCellValue(businessData.getNewUsers());
            }

        //5.将excel数据写入到浏览器 根据httpServletResponse获取输出流
            ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
            excel.write(servletOutputStream);

        //6.关闭流
            inputStream.close();
            servletOutputStream.close();
            excel.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
